package com.core.common.utils;

import cn.hutool.http.HttpRequest;
import cn.hutool.http.HttpUtil;
import cn.hutool.json.JSON;
import cn.hutool.json.JSONUtil;
import com.core.common.exception.BDException;
import lombok.Data;
import lombok.SneakyThrows;
import lombok.experimental.Accessors;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @program: gims
 * @description:
 * @author: hl
 * @create: 2024-05-30 18:19
 **/
@Slf4j
public class ExcelReadUtil {


    @SneakyThrows
    public static List<Map<String, Object>> parseFromFile(File file, String[] tableTitles) {
        return parseFromFile(file, 0, 0, tableTitles);
    }

    @SneakyThrows
    public static List<Map<String, Object>> parseFromFile(File file, int sheetIndex,
                                                          int rowIndex, String[] tableTitles) {
        List<Map<String, Object>> result = new ArrayList<>();
        if (null == file) {
            throw new BDException(600, "请上传XLSX格式文件");
        }
        String originalFilename = file.getName();
        if (originalFilename.indexOf(".xlsx") == -1) {
            throw new BDException(600, "请上传XLSX格式文件");
        }
        try (InputStream fis = new FileInputStream(file)) {
            result.clear();
            result.addAll(parseXLSX(fis, sheetIndex, rowIndex, tableTitles));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;

    }


    @SneakyThrows
    public static List<Map<String, Object>> parseFromURl(String urls, String[] tableTitles) {
        return parseFromURl(urls, 0, 0, tableTitles);
    }

    @SneakyThrows
    public static List<Map<String, Object>> parseFromURl(String url, int sheetIndex,
                                                         int rowIndex, String[] tableTitles) {
//        URL url = new URL(urls);
//        HttpURLConnection conn = (HttpURLConnection) url.openConnection();
//        conn.setRequestMethod("GET");
//        conn.setConnectTimeout(5 * 1000);
//        InputStream inputStream = conn.getInputStream();
        InputStream inputStream = HttpRequest.get(url).execute().bodyStream();

        return parseXLSX(inputStream, sheetIndex, rowIndex, tableTitles);
    }


    public static List<Map<String, Object>> parseFrom(MultipartFile file, String[] tableTitles) {
        return parseFrom(file, 0, 0, tableTitles);
    }

    public static List<Map<String, Object>> parseFrom(MultipartFile file, int sheetIndex,
                                                      int rowIndex, String[] tableTitles) {
        List<Map<String, Object>> result = new ArrayList<>();
        if (null == file) {
            throw new BDException(600, "请上传XLSX格式文件");
        }
        String originalFilename = file.getOriginalFilename();
        if (originalFilename.indexOf(".xlsx") == -1) {
            throw new BDException(600, "请上传XLSX格式文件");
        }
        try (InputStream fis = file.getInputStream()) {
            if (file.getOriginalFilename().toLowerCase(Locale.ROOT).contains(".xlsx")) {
                result.clear();
                result.addAll(parseXLSX(fis, sheetIndex, rowIndex, tableTitles));
            } else {
                throw new BDException(600, "请上传XLSX格式文件");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }


    private static List<Map<String, Object>> parseXLSX(InputStream fis,
                                                       int sheetIndex,
                                                       int rowIndex,
                                                       String[] tableTitles) {
        List<Map<String, Object>> datas = new ArrayList<>();

        try (XSSFWorkbook book = new XSSFWorkbook(fis);) {
            XSSFSheet sheet = book.getSheetAt(sheetIndex);
            String sheetName = sheet.getSheetName();
            if (sheet.getLastRowNum() < 0) {
                return datas;
            }
            if (null == tableTitles || tableTitles.length == 0) {
                //获取标题
                XSSFRow rowTitle = sheet.getRow(rowIndex++);
                tableTitles = new String[rowTitle.getLastCellNum() + 1];
                for (int i = 0; i < rowTitle.getLastCellNum(); i++) {
                    XSSFCell cell = rowTitle.getCell(i);
                    tableTitles[i] = cell.getStringCellValue();
                }
            }
            if (sheet.getLastRowNum() + 1 < tableTitles.length) {
                throw new BDException(600, "数据列数小于提取标题数");
            }
            for (; rowIndex < sheet.getLastRowNum(); rowIndex++) {
                XSSFRow row = sheet.getRow(rowIndex);
                Map<String, Object> data = new HashMap<>();
                if (row == null) {
                    continue;
                }
                for (int i = 0; i < row.getLastCellNum(); i++) {
                    XSSFCell cell = row.getCell(i);
                    if (CellType.NUMERIC.equals(cell.getCellType())) {
                        data.put(tableTitles[i], cell.getNumericCellValue());
                    }
                    data.put(tableTitles[i], cell.getStringCellValue());
                }
                datas.add(data);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return datas;
    }


    @SneakyThrows
    public static Map<String, List<Map<String, Object>>> parseAllFrom(MultipartFile file) {
        Map<String, List<Map<String, Object>>> result = new HashMap<>();
        if (null == file) {
            throw new BDException(600, "请上传XLSX格式文件");
        }
        String originalFilename = file.getOriginalFilename().toLowerCase();
        if (originalFilename.indexOf(".xlsx") == -1) {
            throw new BDException(600, "请上传XLSX格式文件");
        }
        try (InputStream fis = file.getInputStream()) {
            result.clear();
            result.putAll(parseAllXLSX(fis, 0));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }


    @SneakyThrows
    public static Map<String, List<Map<String, Object>>> parseAllFromFile(File file, int sheetIndex) {
        Map<String, List<Map<String, Object>>> result = new HashMap<>();
        if (null == file || !file.exists() || !file.isFile()) {
            throw new BDException(600, "请上传XLSX格式文件");
        }
        String originalFilename = file.getName().toLowerCase();
        if (originalFilename.indexOf(".xlsx") == -1) {
            throw new BDException(600, "请上传XLSX格式文件");
        }
        try (InputStream fis = new FileInputStream(file)) {
            result.clear();
            result.putAll(parseAllXLSX(fis, sheetIndex));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;

    }


    private static Map<String, List<Map<String, Object>>> parseAllXLSX(InputStream fis,
                                                                       int sheetIndex) {
        Map<String, List<Map<String, Object>>> dataMap = new HashMap<>();

        try (XSSFWorkbook book = new XSSFWorkbook(fis);) {
            log.info("sheetnumber:{}",book.getNumberOfSheets());
            if (sheetIndex >= book.getNumberOfSheets()) {
                return dataMap;
            }
            //遍历sheet 工作表
            for (; sheetIndex < book.getNumberOfSheets(); sheetIndex++) {
                XSSFSheet sheet = book.getSheetAt(sheetIndex);
                String sheetName = sheet.getSheetName();
                if (sheet.getLastRowNum() < 0) {
                    continue;
                }
                List<Map<String, Object>> datas = new ArrayList<>();
                dataMap.put(sheetName, datas);

                //跳过首列存在合并的行，取第一个完整行做为标题
                int rowIndex = 0;
                List<String> tableTitles = new LinkedList<>();
                for (; rowIndex < sheet.getLastRowNum(); rowIndex++) {
                    XSSFRow rowTitle = sheet.getRow(rowIndex);
                    for (int i = 0; i < rowTitle.getLastCellNum(); i++) {
                        XSSFCell cell = rowTitle.getCell(i);
                        if (null == cell){
                            break;
                        }
                        boolean isMerged = sheet.getMergedRegions().stream().filter(region -> region.isInRange(cell)).findAny().isPresent();
                        if (isMerged) {
                            break;
                        }
                        try {
                            tableTitles.add(cell.getStringCellValue());
                        } catch (Exception e) {
                            log.error("sheetname:{} rowindex:{} cellIndex:{} cellNum:{}",sheetName,rowIndex,i,rowTitle.getLastCellNum() );
                            e.printStackTrace();
                        }
                    }
                    tableTitles = tableTitles.stream().filter(t -> StringUtils.isNotBlank(t))
                            .collect(Collectors.toList());
                    if (tableTitles.size() < rowTitle.getLastCellNum()) {
                        //说明有合并的单元格 或无数据列，不适合做标题
                        tableTitles.clear();
                    } else {
                        rowIndex++;
                        break;
                    }
                }
                log.info("title:{}", tableTitles);
                if (tableTitles.isEmpty()){
                    log.error( "sheetName :{}无法读取标题" ,sheetName);
                    continue;
                }

                //遍历行row
                for (; rowIndex < tableTitles.size(); rowIndex++) {
                    XSSFRow row = sheet.getRow(rowIndex);
                    if (null == row) {
                        continue;
                    }
                    Map<String, Object> data = new HashMap<>();
                    for (int i = 0; i < row.getLastCellNum(); i++) {
                        XSSFCell cell = row.getCell(i);
                        if (null == cell) {
                            continue;
                        }

                        if (CellType.NUMERIC.equals(cell.getCellType())) {
                            data.put(tableTitles.get(i), cell.getNumericCellValue());
                        } else {
                            try {
                                data.put(tableTitles.get(i), cell.getStringCellValue());
                            } catch (Exception e) {
                                log.error("读取行: sheetname:{} rowindex:{} cellIndex:{} cellNum:{}",sheetName,rowIndex,i,row.getLastCellNum() );
                                e.printStackTrace();
                            }
                        }
                    }
                    datas.add(data);

                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return dataMap;
    }


    @SneakyThrows
    public static void main(String[] args) {

        Map<String, List<Map<String, Object>>> map = parseAllFromFile(
                new File("C:\\Users\\user\\Desktop\\test.xlsx"),
                0);
        System.out.println(map);
    }


}
